package com.shop.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.shop.bean.Admin;
import com.shop.bean.Goods;

public class BaseDao {
	public static final String URL = "jdbc:mysql://127.0.0.1:3306/shop?characterEncoding=UTF-8";
	public static final String USER = "root";
	public static final String PASS = "123456";
	public static final String DRIVER = "com.mysql.cj.jdbc.Driver";

	public static Connection conn = null;
	public static PreparedStatement ps = null;

	/**
	 * 执行 添加 删除 修改的方法
	 * 
	 * @param String SQL，Object占位符对应的值得数组 没有占位符传入null
	 * @RETURN int 影响行数 -1没有执行
	 */
	public static int executeUpdate(String sql, Object[] param) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);// 预编译sql
			if (param != null) {
				for (int i = 0; i < param.length; i++) {
					ps.setObject(i + 1, param[i]);
				}
			}
			int num = ps.executeUpdate();// 执行sql
			return num;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeAll(conn, ps);
		}
		return -1;
	}

	/**
	 * 执行 添加 删除 修改的方法
	 * 
	 * @param String SQL
	 * @RETURN int 影响行数
	 */
	public static int executeUpdate(String sql) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);// 预编译sql
			int num = ps.executeUpdate();
			return num;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeAll(conn, ps);
		}
		return -1;
	}

	/**
	 * 获得数据库连接
	 */
	public static Connection getConn() {
		try {
			Class.forName(DRIVER);
			Connection conn = DriverManager.getConnection(URL, USER, PASS);
			if (conn != null) {
				System.out.println("连接成功");
			}
			return conn;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;
	}

	/**
	 * 释放资源Connection，PreparedStatement，ResultSet
	 */
	public static void closeAll(Connection conn, Statement ps, ResultSet rs) {
		try {
			if (rs != null)
				rs.close();
			if (ps != null)
				ps.close();
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 释放资源Connection，PreparedStatement
	 */
	public static void closeAll(Connection conn, PreparedStatement ps) {
		try {
			if (ps != null)
				ps.close();
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 查询
	 * 
	 * @param sql
	 * @return ResultSet
	 */
	public static ResultSet queryResultSet(String sql) {
		BaseDao.conn = BaseDao.getConn();// 打开连接
		ResultSet rs = null;
		try {
			BaseDao.ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * 查询案例 可借鉴
	 */

//	public Admin queryByAP(String account, String password) {
//		Connection conn = BaseDao.getConn();
//		PreparedStatement s = null;
//		ResultSet rs = null;
//		try {
//			String sql = "SELECT * FROM admin WHERE account = ? and password = ?";
//			s = conn.prepareStatement(sql);
//			s.setString(1, account);
//			s.setString(2, password);
//			rs = s.executeQuery();
//			while (rs.next()) {
//				Admin a = new Admin();
//				a.setId(rs.getInt("id"));
//				a.setAccount(rs.getString("account"));
//				a.setPassword(rs.getString("password"));
//				return a;
//			}
//
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} finally {
//			BaseDao.closeAll(conn, s, rs);
//		}
//		return null;
//	}

//	public List<User> queryAll() {
//		Connection conn=BaseDao.getConn();//打开连接
//		String sql = "SELECT * FROM user";
//		List<User> list = new ArrayList<User>();
//		PreparedStatement ps= null;
//		ResultSet rs=null;
//		try {
//			ps= conn.prepareStatement(sql);
//			rs = ps.executeQuery();			
//			while(rs.next()) {
//				User user = new User();
//				user.setId(rs.getInt("id"));
//				user.setUsername(rs.getString("username"));
//				user.setUserpass(rs.getString("userpass"));
//				user.setSex(rs.getString("sex"));
//				user.setAge(rs.getInt("age"));
//				list.add(user);
//			}
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}finally {
//			BaseDao.closeAll(conn, ps,rs);
//		}
//		return list;
//	}

//	public List<Goods> queryByPageNS(int pageNum, int pageSize) {
//		Connection conn = BaseDao.getConn();// 打开连接
//		String sql = "SELECT * FROM goods LIMIT ?,?";
//		List<Goods> list = new ArrayList<Goods>();
//		PreparedStatement ps = null;
//		ResultSet rs = null;
//		try {
//			ps = conn.prepareStatement(sql);
//			ps.setInt(1, (pageNum - 1) * pageSize);
//			ps.setInt(2, pageSize);
//			rs = ps.executeQuery();
//			while (rs.next()) {
//				Goods g = new Goods();
//				g.setId(rs.getInt("id"));
//				g.setGoodsName(rs.getString("goodsName"));
//				g.setGoodsFactory(rs.getString("goodsFactory"));
//				g.setGoodsType(rs.getString("goodsType"));
//				g.setGoodsPrice(rs.getDouble("goodsPrice"));
//				g.setGoodsNum(rs.getInt("goodsNum"));
//				list.add(g);
//			}
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} finally {
//			BaseDao.closeAll(conn, ps, rs);
//		}
//		return list;
//	}

}
